#!bin/bash


if [ -n "$1" ] ; then
  data_date=$1
fi



TMP_USER_ACTION_LOG_DIS_SQL="
DROP TABLE IF EXISTS wyy_music_warehouse.tmp_user_action_log_dis;
CREATE TABLE wyy_music_warehouse.tmp_user_action_log_dis
AS
SELECT log_id,
       user_id,
       device_id,
       action_time,
       action_type,
       song_id,
       artist_id,
       album_id,
       play_duration,
       network_type,
       ip,
       province,
       city,
       client_version,
       os_type
FROM wyy_music_warehouse.ods_user_action_log
WHERE dt = '${data_date}'
GROUP BY log_id, user_id, device_id, action_time, action_type, song_id, artist_id, album_id, play_duration,
         network_type, ip, province, city, client_version, os_type;
;
"

TMP_USER_ACTION_CLEANED_SQL="
DROP TABLE IF EXISTS wyy_music_warehouse.tmp_user_action_cleaned;
CREATE TABLE IF NOT EXISTS wyy_music_warehouse.tmp_user_action_cleaned
AS
SELECT t.log_id,
       IF(t.user_id = '', 'unknown', t.user_id)                                         AS user_id,

       COALESCE(t.device_id, 'unknown_device')                                          AS device_id,

       CASE
           WHEN t.action_time <= 0 THEN '9999-12-31 00:00:00'
           ELSE from_unixtime((t.action_time)/1000)
           END                                                                        AS action_time,

       CASE
           WHEN t.action_type NOT IN ('play', 'download', 'share', 'collect')
               OR t.action_type = ''
               THEN 'unknown_action'
           ELSE t.action_type
           END                                                                          AS action_type,

       COALESCE(t.song_id, 'unknown_song')                                              AS song_id,
       COALESCE(t.artist_id, 'unknown_artist')                                          AS artist_id,
       COALESCE(t.album_id, 'unknown_album')                                            AS album_id,

       CASE
           WHEN t.action_type = 'play'
               AND t.play_duration > s.music_duration
               THEN t.play_duration
           ELSE t.play_duration
           END                                                                          AS play_duration,

       CASE
           WHEN t.network_type NOT IN ('4G', '5G', 'WIFI', '2G', '3G')
               THEN 'unknown_network'
           ELSE t.network_type
           END                                                                          AS network_type,
       COALESCE(t.province, 'unknown_province')                                         AS province,
       COALESCE(t.city, 'unknown_city')                                                 AS city,

       REGEXP_EXTRACT(t.client_version, '^\\d+\\.\\d+', 0)                              AS client_version,

       CASE
           WHEN t.os_type NOT IN ('ios', 'android', 'windows', 'mac')
               THEN 'unknown_os'
           ELSE t.os_type
           END                                                                          AS os_type,
       s.song_name,
       s.music_play_count_total,
       s.album_play_count_total,
       s.music_duration,
       s.artist_name,
       s.album_genre,
       c.vip_level,
       c.vip_expire_date,
       c.register_date as client_register_date

FROM wyy_music_warehouse.tmp_user_action_log_dis t
         LEFT JOIN wyy_music_warehouse.dim_music_artist_album_info s
                   ON t.song_id = s.song_id
         LEFT JOIN wyy_music_warehouse.ods_user_info c ON c.user_id = t.user_id
WHERE s.dt = '${data_date}' and c.dt='${data_date}';
"

TMP_USER_ACTION_END_SQL="
DROP TABLE IF EXISTS wyy_music_warehouse.tmp_user_action_end;
CREATE TABLE  wyy_music_warehouse.tmp_user_action_end
AS
WITH tmp as (SELECT DISTINCT device_id
             FROM (
                      SELECT
                          device_id, song_id, action_time
                           , count(1) OVER (
                          PARTITION BY device_id, song_id ORDER BY action_time)
                          AS cnt
                      FROM wyy_music_warehouse.tmp_user_action_cleaned
                  ) t1
             WHERE t1.cnt > 100
             UNION
             SELECT DISTINCT device_id
             FROM (
                      SELECT
                          device_id, song_id, song_id, next_event_time
                           , (next_event_time - action_time) AS interval_ms
                      FROM (
                               SELECT
                                   device_id, song_id, action_time
                                    , lead(action_time, 1, 0) OVER (PARTITION BY device_id, song_id ORDER BY action_time) AS next_event_time
                               FROM wyy_music_warehouse.tmp_user_action_cleaned
                           ) t1
                  ) t2
             GROUP BY device_id, song_id, interval_ms
             HAVING count(1) > 5)
SELECT * FROM
    wyy_music_warehouse.tmp_user_action_cleaned
WHERE device_id  in  (select device_id from tmp );
"

DWD_EVENT_LOG_DETAIL_SQL="
INSERT INTO wyy_music_warehouse.dwd_event_log_detail PARTITION (dt='${data_date}')
SELECT log_id,
       action_time,
       action_type,
       play_duration          AS client_play_duration,
       province               AS client_province,
       city                   AS client_city,
       client_version,
       device_id              AS client_device_id,
       os_type                AS client_os_type,
       network_type           AS client_network_type,
       client_register_date,
       song_id,
       song_name,
       music_play_count_total AS song_play_count_total,
       music_duration         AS song_duration,
       artist_id,
       artist_name,
       album_id,
       album_genre,
       album_play_count_total,
       user_id,
       vip_level              AS user_vip_level,
       vip_expire_date        AS user_vip_expire_date
FROM wyy_music_warehouse.tmp_user_action_end
;
"

/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -p 123456 -e "${TMP_USER_ACTION_LOG_DIS_SQL}${TMP_USER_ACTION_CLEANED_SQL}${TMP_USER_ACTION_END_SQL}${DWD_EVENT_LOG_DETAIL_SQL}"

